﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using System.Threading;

namespace Pentakill
{
    public partial class Products : Form
    {
        SqlConnection con = new SqlConnection();
        SqlDataAdapter da;
        DataSet ds;
        SqlCommandBuilder cb;
        DataTable dt;
        DataView dv;

       

        public Products()
        {
            InitializeComponent();
            string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            con.ConnectionString = constr;
            RefreshProduct();
            RefreshCategory();
           
        }

        void InitializeADObject_Product()
        {


            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "LoadProductTable";

            da = new SqlDataAdapter(cmd);
            cb = new SqlCommandBuilder(da);

            ds = new DataSet();
            da.FillSchema(ds, SchemaType.Mapped);
            da.Fill(ds, "Production.Products");
        }
        void LoadProductInfo()
        {
            
            dt = ds.Tables["Production.Products"];
            dv = new DataView(dt);
            dgvProducts.DataSource = dt;
            dgvProducts.Columns["productname"].Width = 170;
            dgvProducts.Columns["categoryid"].Width = 104;
            dgvProducts.Columns[4].DefaultCellStyle.Format = "#,###,###.###";
            dgvProducts.Columns[0].HeaderText = "Product ID";
            dgvProducts.Columns[1].HeaderText = "Product's Name";
            dgvProducts.Columns[2].HeaderText = "Supplier ID";
            dgvProducts.Columns[3].HeaderText = "Category ID";
            dgvProducts.Columns[4].HeaderText = "Unit Price";
            dgvProducts.Columns[5].HeaderText = "Discontinued";
        }
        void LoadCategories()
        {
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;

            SqlDataAdapter da = new SqlDataAdapter("SELECT categoryid, categoryname FROM Production.Categories", con);
            DataTable dt = new DataTable("Production.Categories");
            da.Fill(dt);
            cmbCatName.DataSource = dt;
            cmbCatName.DisplayMember = "categoryname";//cot hien ra trong giao dien
            cmbCatName.ValueMember = "categoryid";



            con.Close();

        }
        void LoadSuppliers()
        {
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;

            SqlDataAdapter da = new SqlDataAdapter("SELECT supplierid, companyname FROM Production.Suppliers", con);
            DataTable dt = new DataTable("Production.Suppliers");
            da.Fill(dt);
            cmbSupName.DataSource = dt;
            cmbSupName.DisplayMember = "companyname";//cot hien ra trong giao dien
            cmbSupName.ValueMember = "supplierid";
            con.Close();
        }
        private void RefreshCategory()
        {   
           
            InitializeADObject_Category();
            LoadCategoryInfo();
            btnUpdateCat.Enabled = false;
            btnDeleteCat.Enabled = false;
           
        }
        private void RefreshProduct()
        {   
            
            InitializeADObject_Product();
            LoadProductInfo();
            LoadCategories();
            LoadSuppliers();
            btnUpdate.Enabled = false;
            btnDelete.Enabled = false;
          

        }
        void InitializeADObject_Category()
        {
           

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "LoadCategoryTable";

            da = new SqlDataAdapter(cmd);
            cb = new SqlCommandBuilder(da);

            ds = new DataSet();
            da.FillSchema(ds, SchemaType.Mapped);
            da.Fill(ds, "Production.Categories");
        }
        void LoadCategoryInfo()
        {
            dt = ds.Tables["Production.Categories"];
            dv = new DataView(dt);
            dgvCategories.DataSource = dt;
            dgvCategories.Columns["description"].Width = 450;
            dgvCategories.Columns["categoryname"].Width = 130;
            dgvCategories.Columns["categoryid"].Width = 117;
            dgvCategories.Columns[0].HeaderText = "Category ID";
            dgvCategories.Columns[1].HeaderText = "Category Name";
            dgvCategories.Columns[2].HeaderText = "Description";
        }


        private void ClearProductTab()
        {
            btnUpdate.Enabled = false;
            btnDelete.Enabled = false;
            btnAdd.Enabled = true;
            txtProID.Text = "";
            txtProName.Text = "";
            txtPrice.Text = "";
            chkDiscontinued.Checked = false;
            errorProvider1.Clear();
            
        }
        private void ClearCategoryTab()
        {
            btnUpdateCat.Enabled = false;
            btnDeleteCat.Enabled = false;
            btnAddCat.Enabled = true;
            txtCatID.Text = "";
            txtCatName.Text = "";
            txtDescription.Text = "";
            errorProvider1.Clear();
        }
        bool validateInput()
        {

            bool error = false;
            string name = txtProName.Text;
            if (name.Trim().Length == 0||name==null)
            {
                errorProvider1.SetError(txtProName, "Please enter Product Name!");

                error = true;
            }

            string price = txtPrice.Text;
            if (price.Trim().Length == 0 || price == null)
            {
                errorProvider1.SetError(txtPrice, "Please enter Price!");

                error = true;
            }
            
            if (error == true)
            {
                return false;
            }
            else
            {
                errorProvider1.Clear();
            }
            return true;
        }
        void AddNewProduct()
        {
            if (validateInput())
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "AddProduct";

                SqlParameter param = new SqlParameter("@Proname", SqlDbType.NVarChar);
                param.Size = 40;
                param.Value = txtProName.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@SupID", SqlDbType.Int);

                param.Value = cmbSupName.SelectedValue;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@CatID", SqlDbType.Int);

                param.Value = cmbCatName.SelectedValue;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@Price", SqlDbType.Money);

                param.Value = txtPrice.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@Discontinued", SqlDbType.Bit);
                if (chkDiscontinued.Checked)
                    param.Value = 1;
                else param.Value = 0;
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();

                MessageBox.Show("Add successfully!");
                con.Close();
                RefreshProduct();

            }


        }

        void UpdateProduct()
        {
            if (validateInput())
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "UpdateProduct";

                SqlParameter param = new SqlParameter("@ProID", SqlDbType.Int);
                param.Value = int.Parse(txtProID.Text.ToString());
                cmd.Parameters.Add(param);

                param = new SqlParameter("@Proname", SqlDbType.NVarChar);
                param.Size = 40;
                param.Value = txtProName.Text.ToString();
                cmd.Parameters.Add(param);

                param = new SqlParameter("@SupID", SqlDbType.Int);

                param.Value = cmbSupName.SelectedValue;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@CatID", SqlDbType.Int);

                param.Value = cmbCatName.SelectedValue;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@Price", SqlDbType.Money);

                param.Value = txtPrice.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@Discontinued", SqlDbType.Bit);
                if (chkDiscontinued.Checked)
                    param.Value = 1;
                else param.Value = 0;
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();

                MessageBox.Show("Update successfully!");
                con.Close();
                RefreshProduct();

            }


        }
        private void Products_FormClosing(object sender, FormClosingEventArgs e)
        {
            DialogResult result;
            result = MessageBox.Show("Do you want to Exit?", "Exit", MessageBoxButtons.OKCancel);
            if (result == DialogResult.Cancel)
            {
                e.Cancel = true;
            }
            this.Hide();
            this.Parent = null;
            e.Cancel = true;
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            AddNewProduct();
        }

        private void txtPrice_KeyPress(object sender, KeyPressEventArgs e)
        {
            char decimalChar = '.';
            // Kiểm tra xem phím nhấn có phải là số hoặc phím chức năng ko
            if (Char.IsDigit(e.KeyChar) || Char.IsControl(e.KeyChar)) { }
            // Cho phép nhập kí tự '.' để biểu diễn thập phân và chỉ cho
            // nhập kí tự '.' 1 lần
            else if (e.KeyChar == decimalChar && txtPrice.Text.IndexOf(decimalChar.ToString()) == -1) { }
            else
            {
                e.Handled = true;
            }
        }

        private void dgvProducts_SelectionChanged(object sender, EventArgs e)
        {
            if (dgvProducts.SelectedRows.Count > 0)
            {
                
                btnUpdate.Enabled = true;
                btnDelete.Enabled = true;
                btnAdd.Enabled = false;
                DataGridViewRow r = dgvProducts.SelectedRows[0];

                txtProID.Text = r.Cells[0].Value.ToString();
                txtProName.Text = r.Cells[1].Value.ToString();
                cmbSupName.SelectedValue = r.Cells[2].Value;
                cmbCatName.SelectedValue = r.Cells[3].Value;
                txtPrice.Text = r.Cells[4].Value.ToString();
                chkDiscontinued.Checked = (bool)r.Cells[5].Value;

            }
        }
        
        private void btnUpdate_Click(object sender, EventArgs e)
        {
            UpdateProduct();
        }


        void DeleteProduct()
        {
            con.Open();
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "DeleteProduct";

                SqlParameter param = new SqlParameter("@ProID", SqlDbType.Int);
                param.Value = int.Parse(txtProID.Text.ToString());
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();
                MessageBox.Show("Delete successfully!");
             
            }
            catch (Exception e)
            {

                MessageBox.Show(e.Message);
            }
             
            con.Close();
     
            RefreshProduct();
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            DeleteProduct();
        }

        void SearchbyName()
        {
            RefreshProduct();
            dv = new DataView(dt);
            dv.RowFilter = string.Format("productname like '%{0}%' ", txtSearchByName.Text);
            dgvProducts.DataSource = dv;
        }

        private void txtSearchByName_KeyPress(object sender, KeyPressEventArgs e)
        {
            SearchbyName();
        }

        void LoadSuppliersFilter()
        {
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;

            SqlDataAdapter da = new SqlDataAdapter("SELECT supplierid, companyname FROM Production.Suppliers", con);
            DataTable dt = new DataTable("Production.Suppliers");
            da.Fill(dt);
            cmbValue.DataSource = dt;
            cmbValue.DisplayMember = "companyname";//cot hien ra trong giao dien
            cmbValue.ValueMember = "supplierid";
            con.Close();
        }
        void LoadCategoryFilter()
        {
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;

            SqlDataAdapter da = new SqlDataAdapter("SELECT categoryid, categoryname FROM Production.Categories", con);
            DataTable dt = new DataTable("Production.Categories");
            da.Fill(dt);
            cmbValue.DataSource = dt;
            cmbValue.DisplayMember = "categoryname";//cot hien ra trong giao dien
            cmbValue.ValueMember = "categoryid";
            con.Close();
        }
        void LoadDisFilter()
        {
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;

            SqlDataAdapter da = new SqlDataAdapter("SELECT DISTINCT discontinued FROM Production.Products", con);
            DataTable dt = new DataTable("Production.Products");
            da.Fill(dt);
            cmbValue.DataSource = dt;
            cmbValue.DisplayMember = "discontinued";//cot hien ra trong giao dien
            cmbValue.ValueMember = "discontinued";
            con.Close();
        }
        void FilterBySupplier()
        {

            RefreshProduct();
            dv = new DataView(dt);
            dv.RowFilter = string.Format("supplierid = {0} ", cmbValue.SelectedValue);
            dgvProducts.DataSource = dv;
        }
        void FilterByCategory()
        {

            RefreshProduct();
            dv = new DataView(dt);
            dv.RowFilter = string.Format("categoryid = {0} ", cmbValue.SelectedValue);
            dgvProducts.DataSource = dv;
        }
        void FilterByDis()
        {

            RefreshProduct();
            dv = new DataView(dt);
            dv.RowFilter = string.Format("discontinued = {0} ", cmbValue.SelectedValue);
            dgvProducts.DataSource = dv;
        }


        void Filter()
        {
            if (cmbFilter.SelectedIndex == 0)
            {
                cmbValue.Enabled = false;
                RefreshProduct();
                dv = new DataView(dt);
                dgvProducts.DataSource = dv;
            }
            else if (cmbFilter.SelectedIndex == 1)
            {
                cmbValue.Enabled = true;
                LoadSuppliersFilter();
                
            }
            else if (cmbFilter.SelectedIndex == 2)
            {
                cmbValue.Enabled = true;
                LoadCategoryFilter();
            }
            else if (cmbFilter.SelectedIndex == 3)
            {
                cmbValue.Enabled = true;
                LoadDisFilter();
            }
        }

       

        private void cmbFilter_SelectionChangeCommitted(object sender, EventArgs e)
        {
            Filter();
        }

        private void cmbValue_SelectionChangeCommitted(object sender, EventArgs e)
        {
            if (cmbFilter.SelectedIndex == 1)
            {

                FilterBySupplier();
            }
            else if (cmbFilter.SelectedIndex == 2)
            {

                FilterByCategory();
            }
            else if (cmbFilter.SelectedIndex == 3)
            {

                FilterByDis();
            }
        }

        private void btnNew_Click(object sender, EventArgs e)
        {
            RefreshProduct();
            cmbFilter.SelectedIndex = 0;
            cmbValue.Enabled = false;
            txtSearchByName.Text = "";
            ClearProductTab();
         

        }

        private void txtSearchByName_MouseClick(object sender, MouseEventArgs e)
        {
            //cmbValue.Enabled = false;
            //cmbFilter.Enabled = false;
        }

        private void cmbFilter_Click(object sender, EventArgs e)
        {
            //txtSearchByName.Enabled = false;
        }

        bool validateInput_Category()
        {

            bool error = false;
            string name = txtCatName.Text;
            if (name.Trim().Length == 0 )
            {
                errorProvider1.SetError(txtCatName, "Please enter Category Name!");

                error = true;
            }

            string des = txtDescription.Text;
           
            if (des.Trim().Length == 0 || des == null)
            {
                errorProvider1.SetError(txtDescription, "Please enter Description!");
                error = true;
            }

            if (error == true)
            {
                return false;
            }
            else
            {
                errorProvider1.Clear();
            }
            return true;
        }

        private void dgvCategories_SelectionChanged(object sender, EventArgs e)
        {
            if (dgvCategories.SelectedRows.Count > 0)
            {

                btnUpdateCat.Enabled = true;
                btnDeleteCat.Enabled = true;
                btnAddCat.Enabled = false;
                DataGridViewRow r = dgvCategories.SelectedRows[0];

                txtCatID.Text = r.Cells[0].Value.ToString();
                txtCatName.Text = r.Cells[1].Value.ToString();
                txtDescription.Text = r.Cells[2].Value.ToString();

            }

        }
        void AddNewCategory()
        {
            if (validateInput_Category())
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "AddCategory";

                SqlParameter param = new SqlParameter("@categoryName", SqlDbType.NVarChar);
                param.Size = 15;
                param.Value = txtCatName.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@description", SqlDbType.NVarChar);
                param.Size = 200;
                param.Value = txtDescription.Text;
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();

                MessageBox.Show("Add successfully!");
                con.Close();
                RefreshCategory();

            }


        }
        void UpdateCategory()
        {
            if (validateInput_Category())
            {
                con.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "UpdateCategory";

                SqlParameter param = new SqlParameter("@categoryName", SqlDbType.NVarChar);
                param.Size = 15;
                param.Value = txtCatName.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@description", SqlDbType.NVarChar);
                param.Size = 200;
                param.Value = txtDescription.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@categoryID", SqlDbType.Int);
                param.Value = int.Parse(txtCatID.Text.ToString());
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();

                MessageBox.Show("Update successfully!");
                con.Close();
                RefreshCategory();

            }

        }
        private void btnAddCat_Click(object sender, EventArgs e)
        {
            AddNewCategory();
        }

        private void btnNewCat_Click(object sender, EventArgs e)
        {
            RefreshCategory();
            txtSearchByCatName.Text = "";
            ClearCategoryTab();

        }

        private void btnUpdateCat_Click(object sender, EventArgs e)
        {
            UpdateCategory();
        }
        void DeleteCategory()
        {
            con.Open();
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "DeleteCategory";

                SqlParameter param = new SqlParameter("@categoryID", SqlDbType.Int);
                param.Value = int.Parse(txtCatID.Text.ToString());
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();
                MessageBox.Show("Delete successfully!");

            }
            catch (Exception e)
            {

                MessageBox.Show(e.Message);
            }

            con.Close();

            RefreshCategory();
            
        }

        private void btnDeleteCat_Click(object sender, EventArgs e)
        {
            DeleteCategory();
        }

        void SearchbyCatName()
        {
            RefreshCategory();
            dv = new DataView(dt);
            dv.RowFilter = string.Format("categoryname like '%{0}%' ", txtSearchByCatName.Text);
            dgvCategories.DataSource = dv;
        }

        private void txtSearchByCatName_KeyPress(object sender, KeyPressEventArgs e)
        {
            SearchbyCatName();
        }
    }
}
